{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "706594b5-8a49-425e-b8cc-5283a9b37cf5",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pymysql\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import pymysql.cursors\n",
    "\n",
    "class MySQLUtils(object):\n",
    "    def __init__(self):\n",
    "        self.conn = pymysql.connect(\n",
    "            host='127.0.0.1',\n",
    "            user='root',\n",
    "            passwd='root',\n",
    "            db='tushare',\n",
    "            port=3306,\n",
    "            charset='utf8'\n",
    "        )\n",
    "    \n",
    "    def get_scenic_data(self):\n",
    "        cursor = self.conn.cursor()\n",
    "        sql = \"\"\"\n",
    "        SELECT t.tourist_agency_name, rel.id_no, LEFT(rel.id_no, 2) as province_code,\n",
    "        DAYOFWEEK(gate.create_time) as non_weekend,\n",
    "        CAST(SUBSTRING(rel.id_no, 7, 4) as unsigned) as birth_year\n",
    "        FROM ticket_order_user_rel rel\n",
    "        LEFT JOIN ticket_order t ON t.id = rel.order_id\n",
    "        LEFT JOIN order_user_gate_rel gate ON gate.ticket_rel_id = rel.id\n",
    "        WHERE t.pay_time != \"\" AND t.tourist_agency_name != \"\"\n",
    "        \"\"\"\n",
    "        cursor.execute(sql)\n",
    "        ret = cursor.fetchall()\n",
    "        \n",
    "        df = pd.DataFrame(ret)\n",
    "        # Process weekend flag\n",
    "        df['non_weekend'] = df['non_weekend'].apply(lambda x: 1 if x not in [1, 7] else 0)\n",
    "        \n",
    "        # Add valid ID flag\n",
    "        df['valid_id'] = df['id_no'].apply(lambda x: 1 if x and str(x).strip() != '' else 0)\n",
    "        \n",
    "        # Calculate out-of-province and elderly flags\n",
    "        df['out_province_ratio'] = df.apply(\n",
    "            lambda x: 1 if (x['valid_id'] and x['province_code'] != 44) else 0 if x['valid_id'] else np.nan,\n",
    "            axis=1\n",
    "        )\n",
    "        df['elderly_ratio'] = df.apply(\n",
    "            lambda x: 1 if (x['valid_id'] and 2025 - x['birth_year'] >= 60) else 0 if x['valid_id'] else np.nan,\n",
    "            axis=1\n",
    "        )\n",
    "        \n",
    "        # Group and aggregate\n",
    "        result = df.groupby(['tourist_agency_name']).agg(\n",
    "            total_visitors=('id_no', 'count'),  # Total visitors\n",
    "            valid_visitors=('valid_id', 'sum'),  # Valid ID visitors\n",
    "            out_province=('out_province_ratio', 'sum'),  # Out-of-province visitors\n",
    "            elderly=('elderly_ratio', 'sum'),  # Elderly visitors\n",
    "            non_weekend=('non_weekend', 'mean')  # Non-weekend ratio\n",
    "        ).reset_index()\n",
    "        \n",
    "        # Calculate actual ratios\n",
    "        result['out_province_ratio'] = result['out_province'] / result['valid_visitors'].replace(0, np.nan)\n",
    "        result['elderly_ratio'] = result['elderly'] / result['valid_visitors'].replace(0, np.nan)\n",
    "        \n",
    "        # Clean intermediate columns\n",
    "        result = result.drop(['out_province', 'elderly'], axis=1)\n",
    "        result['out_province_ratio'] = result['out_province_ratio'].fillna(0)\n",
    "        result['elderly_ratio'] = result['elderly_ratio'].fillna(0)\n",
    "        result.to_csv('scenic_data.csv')\n",
    "        return result"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
